/**
 * Copyright 2008-2009. Chongqing Communications Industry Services Co.,Ltd Information Technology Branch. All rights
 * reserved. <a>http://www.cqcis.com</a>
 */
package com.cq.bd.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowCallbackHandler;

import com.cq.base.domain.KeyLabel;
import com.cq.base.domain.Page;
import com.cq.base.orm.jdbc.BaseJdbcDao;
import com.cq.bd.domain.BInventory;
import com.cq.bd.domain.BTake;
import com.cq.bd.utils.StringUtil;

/**
 * 统计
 * 
 * @author huangmy
 * @date   2009-10-7
 */
public class BTakeJdbcDao extends BaseJdbcDao {
    private static String bloodTypeCountsSql = "select blood_type, count(*) from b_take group by 1";
    
    /**
     * 查询各血型数
     * 
     * @return
     */
    public List<KeyLabel> queryBloodTypeCounts() {
        final List<KeyLabel> kls = new ArrayList<KeyLabel>();
        super.getJdbcTemplate().query(bloodTypeCountsSql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                String key = rs.getString(1);
                String label = rs.getString(2);
                
                kls.add(new KeyLabel(key, label));
            }
        });
        return kls;
    }
    
    /**
     * 根据条件查询
     * 
     * @param filter
     * @param like
     * @param sort
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page pageCondTakes(Map<String, String> filter, Map<String, String> like, Map<String, String> sort,
            int pageNo, int pageSize) {
        String bloodType = filter.get("bloodType");
        String searchName = filter.get("searchName");
        String searchNum = filter.get("searchNum");
        if (StringUtil.hasText(bloodType)) {
            String countSql = "select count(*) from b_take where blood_type='" + bloodType + "'";          
            String querySql = "select * from b_take where blood_type='" + bloodType + "' limit " + pageSize;
            // 根据血型查询
            int sum = getSimpleJdbcTemplate().queryForInt(countSql);
            Page page = getBaseJdbcTemplate().pagedQuery(querySql, pageNo, pageSize, sum, BTake.class);
            return page;
        } else if (StringUtil.hasText(searchName) || StringUtil.hasText(searchNum)) {
            // 根据名称或者身份证好吗查询
            String countSql = null;
            String querySql = null;
            
            if (StringUtil.hasText(searchName) && !StringUtil.hasText(searchNum)) {
                // 只是名称
                countSql = "select count(*) from b_take where blood_donor='" + searchName + "'";
                querySql = "select * from b_take where blood_donor='" + searchName + "' limit " + pageSize;
            } else if (!StringUtil.hasText(searchName) && StringUtil.hasText(searchNum)) {
                // 只是身份证
                countSql = "select count(*) from b_take where blood_num='" + searchNum + "'";
                querySql = "select * from b_take where blood_num='" + searchNum + "' limit " + pageSize;
            } else {
                // 名称和身份证
                countSql = "select count(*) from b_take where blood_donor='" + searchName + "' and blood_num='" + searchNum + "'";
                querySql = "select * from b_take where blood_donor='" + searchName + "' and blood_num='" + searchNum + "' limit " + pageSize;
            }
            
            int sum = getSimpleJdbcTemplate().queryForInt(countSql);
            Page page = getBaseJdbcTemplate().pagedQuery(querySql, pageNo, pageSize, sum, BTake.class);
            return page;
        }
        
        return null;
    }
    
    /**
     * 当前血型库存量
     * 
     * @return
     */
    public List<BInventory> queryBloodResult() {
        String querySql = "select blood_type,sum(blood_volume) from b_inventory group by blood_type";
        final List<BInventory> kls = new ArrayList<BInventory>();
        super.getJdbcTemplate().query(querySql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                String key = rs.getString(1);
                int label = rs.getInt(2);
                
                BInventory bi = new BInventory();
                bi.setBloodType(key);
                bi.setBloodVolume(label);
                kls.add(bi);
            }
        });
        return kls;
    }
}
